package DataLayer.DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import DataLayer.Model.EkstraService;

public class DBEkstraService implements IFDBEkstraService
{
	private Connection con = DBConnection.getInstance().getDBcon();

	@Override
	public ArrayList<EkstraService> getAllEkstraServicer(int delOrdreId)
	{
		return miscWhere("delOrdreId_fk = '" + delOrdreId + "'");
	}

	@Override
	public EkstraService getEkstraService(int ekstraServiceId)
	{
		String wClause = "ekstraServiceId = '" + ekstraServiceId + "'";
		System.out.println("Search ekstraServiceId " + wClause);
		return singleWhere(wClause);
	}

	@Override
	public int insertEkstraService(EkstraService es, int delOrdreId)
			throws Exception
	{
		int nextID = GetMax
				.getMaxId("SELECT MAX(ekstraServiceId) FROM EkstraService");
		nextID = nextID + 1;
		int rc = -1;
		String query = "INSERT INTO EkstraService(ekstraServiceId, beskrivelse, pris, delOrdreId_fk)  VALUES('"
				+
				nextID + "','" +
				//es.getEkstraServiceId() + "','" +
				es.getBeskrivelse() + "','" +
				es.getPris() + "','" +
				delOrdreId + "')";
		System.out.println("insert : " + query);
		try
		{ // insert new DelOrdre
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (SQLException ex)
		{
			System.out.println("EkstraService was not inserted");
		}
		return (rc);
	}

	@Override
	public int updateEkstraService(EkstraService es, int delOrdreId)
	{
		int rc = -1;
		String query = "UPDATE EkstraService SET " +
				"beskrivelse ='" + es.getBeskrivelse() + "', " +
				"pris ='" + es.getPris() + "', " +
				"delOrdreId_fk ='" + delOrdreId + "'" +
				" WHERE ekstraServiceId = '" + es.getEkstraServiceId() + "'";
		System.out.println("Update query:" + query);
		try
		{ // update EkstraService
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (Exception ex)
		{
			System.out.println("Update exception in EkstraService db: " + ex);
		}
		return (rc);
	}

	@Override
	public int deleteEkstraService(EkstraService es)
	{
		int rc = -1;
		String query = "DELETE FROM EkstraService WHERE ekstraServiceId = '" +
				es.getEkstraServiceId() + "'";
		System.out.println(query);
		try
		{ // delete FROM EkstraService
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (Exception ex)
		{
			System.out.println("Delete exception in EkstraService db: " + ex);
		}
		return (rc);
	}

	private ArrayList<EkstraService> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<EkstraService> list = new ArrayList<EkstraService>();
		String query = buildQuery(wClause);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while (results.next())
			{
				EkstraService es = new EkstraService();
				es = buildEkstraService(results);
				list.add(es);
			}
			stmt.close();
		}
		catch (Exception e)
		{
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private EkstraService singleWhere(String wClause)
	{
		ResultSet results;
		EkstraService es = new EkstraService();
		String query = buildQuery(wClause);
		System.out.println(query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next())
			{
				es = buildEkstraService(results);
				stmt.close();
			}
			else
			{
				es = null;
			}
		}
		catch (Exception e)
		{
			System.out.println("Query exception: " + e);
		}
		return es;
	}

	private String buildQuery(String wClause)
	{
		String query = "SELECT ekstraServiceId, beskrivelse, pris FROM EkstraService ";
		if (wClause.length() > 0)
		{
			query += "WHERE " + wClause;
		}
		return query;
	}

	private EkstraService buildEkstraService(ResultSet results)
	{
		EkstraService es = new EkstraService();
		try
		{
			es.setEkstraServiceId(results.getInt("ekstraServiceId"));
			es.setBeskrivelse(results.getString("beskrivelse"));
			es.setPris(results.getDouble("pris"));
		}
		catch (Exception e)
		{
			System.out.println("error in building the EkstraService object");
		}
		return es;
	}
}
